<?php
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;

class DefaultDb_Repositories_PackageToOrderRepository extends EntityRepository
{    
    public function addPackage($order , $package, $cantidad,$price, $totalPrice, $dateSend, $packingGenerated)
    {
        $packageToOrder = new DefaultDb_Entities_PackageToOrder();

        if($packageToOrder !== false)
        {

            $packageToOrder->setOrder($order);
            $packageToOrder->setNamePackage($package->getName());
            $packageToOrder->setWeight($package->getWeight());
            $packageToOrder->setHeight($package->getHeight());
            $packageToOrder->setWidth($package->getWidth());
            $packageToOrder->setDepth($package->getDepth());
            $packageToOrder->setNumPackage($cantidad);
            $packageToOrder->setPrice($price);
            $packageToOrder->setTotalPrice($totalPrice);
            $packageToOrder->setDateSend($dateSend);
            $packageToOrder->setPackagingGenerated($packingGenerated);
            
            $em = $this->getEntityManager();
            $em->persist($packageToOrder);
            $em->flush();

        }
    }
    
    public function getInvoicesUntilDate($dateFin)
    {
        $em = $this->getEntityManager();
        $cnx = $this->getEntityManager()->getConnection();
        $res = $cnx->executeQuery('SELECT sum(m.total_price) as total_price, m.packagingGenerated_id, co.shipping_status 
                                   FROM package_to_order m INNER join m3_commerce_order co ON m.order_id = co.id
                                   WHERE m.dateSend < "'.$dateFin .'" and  m.invoice_id is NULL and co.shipping_status = '.  DefaultDb_Entities_M3CommerceOrder::SHIPPING_STATUS_DELIVERED.' 
                                   GROUP BY m.packagingGenerated_id');
        return $res->fetchAll();
     
    }
    
    public function getInvoicesUntilDateAndDateInvoice($dateFin, $dayInvoice)
    {
        $em = $this->getEntityManager();
        $cnx = $this->getEntityManager()->getConnection();
        $query = 'SELECT sum(m.total_price) as total_price, count(co.shipping_status) num_orders, m.packagingGenerated_id, co.shipping_status 
                    FROM package_to_order m 
                    INNER join m3_commerce_order co ON m.order_id = co.id
                    iNNER join users u on u.id = m.packagingGenerated_id 
                    WHERE m.dateSend < "'.$dateFin .'" and  m.invoice_id is NULL 
                        and co.shipping_status = '.  DefaultDb_Entities_M3CommerceOrder::SHIPPING_STATUS_DELIVERED.' 
                        and u.dayInvoice = '.$dayInvoice.'
                    GROUP BY m.packagingGenerated_id';
        //echo $query;
        $res = $cnx->executeQuery($query);
        return $res->fetchAll();
     
    }
    
    public function getPackagesToOrdersUntilDateNotInvoice($dateFin, $client)
    {
        $query = $this->getEntityManager()->createQuery("SELECT p 
            FROM DefaultDb_Entities_PackageToOrder p 
            WHERE p.dateSend < '$dateFin' 
            AND p.invoice IS NULL 
            AND p.packagingGenerated = " . $client->getId());
        return $query->getResult();
    }

        public function getDateNoGroup($dateFin)
    {
        $em = $this->getEntityManager();
        $query = $em->createQuery('SELECT m FROM DefaultDb_Entities_PackageToOrder m WHERE m.invoice is NULL and m.dateSend < '.$dateFin);
        return $query->getResult();
    
    }
    
}
/*
SELECT `packagingGenerated_id` , sum( total_price )
FROM `package_to_order`
WHERE invoice_id IS NULL
AND dateSend <2012 /08 /06
GROUP BY packagingGenerated_id
LIMIT 0 , 30
*/